package cn.su.dao.impl;

import cn.su.dao.UserDao;
import cn.su.pojo.User;
import cn.su.pojo.fenye;
import cn.su.pojo.role;
import cn.su.utils.BaseDao;
import com.sun.java.browser.plugin2.liveconnect.v1.Result;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDaoimpl implements UserDao {
    @Override
    public User Login(String userCode, String userPassword) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        try {
            String sql="select * from smbms_user where userCode=? and userPassword=? ";
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), new Object[]{userCode, userPassword});

            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }
    public  int xiugai(String oldpass,String newpass){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        System.out.println(oldpass+">>"+newpass);
        String sql="update  smbms_user set userPassword= '"+newpass+"' where userPassword='"+oldpass+"'";
        int update=0;
        try {
        update = queryRunner.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;


    }
    public List<User> User_list(fenye fenye){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        //左连接，右连接等用on，不用where
        StringBuffer sql=new StringBuffer("select a.id,a.userCode,a.userName,a.userPassword,a.gender,a.birthday,a.phone,a.address,a.userRole,a.createdBy,a.creationDate,a.modifyDate,a.modifyBy,b.roleName from smbms_user as a  left join  smbms_role as b on a.userRole=b.id");


      if (fenye.currentPageNo<fenye.totalPageCount){
          sql.append(" limit "+((fenye.currentPageNo-1)*12)+","+12);

      }else if (fenye.currentPageNo==fenye.totalPageCount){
            sql.append(" limit "+((fenye.currentPageNo-1)*12)+","+fenye.totalCount);
      }

        List<User> query=null;
        try {
             query = queryRunner.query(sql.toString(),new BeanListHandler<>(User.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    @Override
    public List<role> role_list() {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="select * from smbms_role";
        List<role> query=null;
        try {
         query = queryRunner.query(sql, new BeanListHandler<>(role.class));

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    @Override
    public List<User> USer_search(String name, String juese) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        StringBuffer sql=new StringBuffer("select  a.id,a.userCode,a.userName,a.userPassword,a.gender,a.birthday,a.phone,a.address,a.userRole,a.createdBy,a.creationDate,a.modifyDate,a.modifyBy,b.roleName  from smbms_user as a  left join  smbms_role as b on a.userRole=b.id  where 1=1 ");
        if (name!=null&&!name.trim().equals("")) {
            sql.append(" and userName = '"+name+"'");
        }
        if (juese!=null&&!juese.trim().equals("")&&!juese.equals("0")) {

            sql.append(" and userRole = "+juese);
        }
        try {
            List<User> query = queryRunner.query(sql.toString(), new BeanListHandler<>(User.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
       return null;

    }
    public int User_add(User user){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="insert into smbms_user(userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate)  values(?,?,?,?,?,?,?,?,?,?)";
      int i=0;
        try {
         i=   queryRunner.update(sql,user.getUserCode(),user.getUserName(),user.getUserPassword(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getCreatedBy(),user.getCreationDate());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  i;
    }

    public  User User_chakan(int id){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        try {
            User query = queryRunner.query("select  a.id,a.userCode,a.userName,a.userPassword,a.gender,a.birthday,a.phone,a.address,a.userRole,a.createdBy,a.creationDate,a.modifyDate,a.modifyBy,b.roleName  from smbms_user as a  left join  smbms_role as b on a.userRole=b.id where a.id= " + id, new BeanHandler<>(User.class));
       return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    public  int User_update(int id,User user){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        try {
            //查询职位的id
            role role = queryRunner.query("select * from smbms_role where roleName= '" + user.getRoleName() + "'", new BeanHandler<>(role.class));
        //修改
            String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=?  where  id="+id;

            int update = queryRunner.update(sql, user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), role.getId(), user.getModifyBy(), user.getModifyDate());
            return update;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
    public  int User_delete(int id){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="delete  from smbms_user where id="+id;
        try {
            int update = queryRunner.update(sql);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public  int  User_count(){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        try {
            Long  query =(Long) queryRunner.query("select count(*) from smbms_user", new ScalarHandler());
            return Integer.parseInt(String.valueOf(query));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    //测试用户Login
    public static void main(String[] args) {
        UserDao userDao=new UserDaoimpl();
        User user = userDao.Login("admin", "12");
        System.out.println(user);
    }
}
